﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using ReadyApps.Common;
using ReadyApps.Data.Exceptions;
using ReadyApps.Data.Wiki;
using ReadyApps.Wiki;

namespace ReadyApps.Data.Oracle
{
    class OracleWikiDataProvider : IWikiDataProvider
    {
        #region Inner Classes

        class QueryParameter
        {
            public string Name { get; private set; }
            public object Value { get; private set; }

            public QueryParameter(string name, object value)
            {
                Name = name;
                Value = value;
            }
        }

        #endregion

        #region Variables and Constants

        private const string QUERY_SYNONYM = "ReadyAppsWikiQuery";
        private const string API_SYNONYM = "ReadyAppsWikiApi";

        private string connectionString;

        #endregion

        #region Public Methods

        public void CreateNewSite(string siteName, bool securityEnabled, string initialPath, string creator)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "CreateNewSite");

            OracleTransaction transaction;
            using (OracleConnection connection = GetConnection())
            {
                connection.Open();
                transaction = connection.BeginTransaction();

                try
                {
                    ExecuteProcedure(
                        connection,
                        API_SYNONYM + ".CreateNewSite",
                        new[] {
                            new QueryParameter("i_SiteName", siteName),
                            new QueryParameter("i_SecurityEnabled", securityEnabled ? "Y" : "N"),
                            new QueryParameter("i_InitialPath", initialPath),
                            new QueryParameter("i_Creator", creator)
                        });

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "CreateNewSite");
        }

        public void CreateNewPage(string siteName, string fullPath, string pageName, string author, PageContent content)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "CreateNewPage");

            OracleTransaction transaction;
            using (OracleConnection connection = GetConnection())
            {
                connection.Open();
                transaction = connection.BeginTransaction();

                try
                {
                    int newPageID = ExecuteFunctionWithReturnID(
                        connection,
                        API_SYNONYM + ".CreateNewPage",
                        new[] {
                            new QueryParameter("i_SiteName", siteName),
                            new QueryParameter("i_FullPath", fullPath),
                            new QueryParameter("i_PageName", pageName),
                            new QueryParameter("i_Author", author),
                            new QueryParameter("i_PageTitle", content.Title),
                            new QueryParameter("i_PageContent", content.Text)
                        });

                    foreach (var link in content.Links)
                    {
                        ExecuteProcedure(
                            connection,
                            API_SYNONYM + ".AddPageLink",
                            new[] {
                                new QueryParameter("i_PageID", newPageID),
                                new QueryParameter("i_RevisionNumber", 1),
                                new QueryParameter("i_PageLinkKey", link.PageLinkKey),
                                new QueryParameter("i_DisplayText", link.DisplayText),
                                new QueryParameter("i_LinkPageID", link.LinkPageID),
                                new QueryParameter("i_Target", link.Target)
                            });
                    }

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "CreateNewPage");
        }

        public void CloneRevisionToHead(int pageID, int revisionNumber, string author)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "CloneRevisionToHead");

            OracleTransaction transaction;
            using (OracleConnection connection = GetConnection())
            {
                connection.Open();
                transaction = connection.BeginTransaction();

                try
                {
                    ExecuteProcedure(
                        connection,
                        API_SYNONYM + ".CloneRevisionToHead",
                        new[] {
                            new QueryParameter("i_PageID", pageID),
                            new QueryParameter("i_Author", author),
                            new QueryParameter("i_RevisionNumber", revisionNumber)
                        });

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "CloneRevisionToHead");
        }

        public void SetConnectionString(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public PageLookup LookupPage(string siteName, string folder, string pageName)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "LookupPage");

            PageLookup lookupData;

            using (OracleConnection connection = GetConnection())
            {
                connection.Open();

                OracleDataReader reader = GetCursorFunctionReader(connection,
                    QUERY_SYNONYM + ".FindWikiPage",
                    new[] {
                        new QueryParameter("i_SiteName", siteName),
                        new QueryParameter("i_Folder", folder),
                        new QueryParameter("i_PageName", pageName)
                    });

                if (reader.Read())
                {
                    int pageID = reader["pageID"].As<int>(WikiController.IMPOSSIBLE_PAGE_ID);

                    if (pageID != WikiController.IMPOSSIBLE_PAGE_ID)
                    {
                        lookupData = new PageLookup(
                            pageID,
                            LoadPageDetails(reader));
                    }
                    else
                    {
                        lookupData = PageLookup.PageNotFoundLookup;
                    }
                }
                else
                {
                    lookupData = PageLookup.SiteNotFoundLookup;
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "LookupPage");
            return lookupData;
        }

        public int GetPageFolder(int pageID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetPageFolder");

            int folderID;

            using (OracleConnection connection = GetConnection())
            {
                connection.Open();

                OracleDataReader reader = GetCursorFunctionReader(connection,
                    QUERY_SYNONYM + ".GetPageFolder",
                    new[] {
                        new QueryParameter("i_PageID", pageID)
                    });

                if (reader.Read())
                {
                    folderID = reader["folderID"].As<int>();
                }
                else
                {
                    throw new PageNotFoundException(pageID);
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetPageFolder");
            return folderID;
        }

        public PageDetails GetPageDetails(int pageID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetPageDetails");

            PageDetails details;

            using (OracleConnection connection = GetConnection())
            {
                connection.Open();

                OracleDataReader reader = GetCursorFunctionReader(connection,
                    QUERY_SYNONYM + ".GetPageDetails",
                    new[] {
                        new QueryParameter("i_PageID", pageID)
                    });

                if (reader.Read())
                {
                    details = LoadPageDetails(reader);
                }
                else
                {
                    throw new PageNotFoundException(pageID);
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetPageDetails");
            return details;
        }

        public string GetPageTitle(int pageID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetPageTitle");

            string pageTitle;

            using (OracleConnection connection = GetConnection())
            {
                connection.Open();

                OracleDataReader reader = GetCursorFunctionReader(connection,
                    QUERY_SYNONYM + ".GetPageTitle",
                    new[] {
                        new QueryParameter("i_PageID", pageID)
                    });

                if (reader.Read())
                {
                    pageTitle = reader["pageTitle"].As<string>();
                }
                else
                {
                    throw new PageNotFoundException(pageID);
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetPageTitle");
            return pageTitle;
        }

        public PermissionSet GetPagePermissions(int pageID, out bool pageSpecificPermissions)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetPagePermissions");

            PermissionSet permissionSet;

            using (OracleConnection connection = GetConnection())
            {
                connection.Open();

                permissionSet = GetPagePermissionsInternal(connection, pageID, out pageSpecificPermissions);
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetPagePermissions");
            return permissionSet;
        }

        public PermissionSet GetFolderPermissions(int folderID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetFolderPermissions");

            PermissionSet permissionSet;

            using (OracleConnection connection = GetConnection())
            {
                connection.Open();

                OracleDataReader reader = GetCursorFunctionReader(connection,
                    QUERY_SYNONYM + ".GetFolderPermissions",
                    new[] {
                        new QueryParameter("i_FolderID", folderID)
                    });

                if (reader.HasRows)
                {
                    reader.Read();
                    permissionSet = new PermissionSet(reader["permissionSetID"].As<int>());
                    do
                    {
                        RolePrivileges privileges = permissionSet.AddRole(reader["roleName"].As<string>());
                        LoadRolePrivileges(reader, privileges);
                    } while (reader.Read());
                }
                else
                {
                    throw new InvalidDataStateException("GetFolderPermissions");
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetFolderPermissions");
            return permissionSet;
        }

        public PermissionSet GetFolderPermissionsForPage(int pageID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetFolderPermissionsForPage");

            PermissionSet permissionSet;

            using (OracleConnection connection = GetConnection())
            {
                connection.Open();

                OracleDataReader reader = GetCursorFunctionReader(connection,
                    QUERY_SYNONYM + ".GetFolderPermissionsForPage",
                    new[] {
                        new QueryParameter("i_PageID", pageID)
                    });

                if (reader.HasRows)
                {
                    reader.Read();
                    permissionSet = new PermissionSet(reader["permissionSetID"].As<int>());
                    do
                    {
                        RolePrivileges privileges = permissionSet.AddRole(reader["roleName"].As<string>());
                        LoadRolePrivileges(reader, privileges);
                    } while (reader.Read());
                }
                else
                {
                    throw new InvalidDataStateException("GetFolderPermissionsForPage");
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetFolderPermissionsForPage");
            return permissionSet;
        }

        public IEnumerable<PageInformation> GetPagesInFolder(int folderID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetPagesInFolder");

            List<PageInformation> pages = new List<PageInformation>();

            using (OracleConnection connection = GetConnection())
            {
                connection.Open();

                OracleDataReader reader = GetCursorFunctionReader(connection,
                    QUERY_SYNONYM + ".GetPagesInFolder",
                    new[] {
                        new QueryParameter("i_FolderID", folderID)
                    });

                while (reader.Read())
                {
                    pages.Add(LoadPageInformation(reader));
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetPagesInFolder");
            return pages;
        }

        public EditPermissions GetEditPermissions(int pageID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetEditPermissions");

            EditPermissions editPermissions = null;

            using (OracleConnection connection = GetConnection())
            {
                connection.Open();

                bool pageSpecific;
                PermissionSet permissionSet = GetPagePermissionsInternal(connection, pageID, out pageSpecific);
                editPermissions = new EditPermissions(permissionSet, !pageSpecific);

                OracleDataReader reader = GetCursorFunctionReader(connection,
                    QUERY_SYNONYM + ".GetPagePermissionTree",
                    new[] {
                        new QueryParameter("i_PageID", pageID)
                    });

                while (reader.Read())
                {
                    editPermissions.Hierarchy.Add(new ObjectPermissionSet(reader["name"].As<string>(), reader["permissionSetID"].As<int>(0)));
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetEditPermissions");
            return editPermissions;
        }

        public void SavePagePermissions(int pageID, EditPermissions editPermissionsData)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "SavePagePermissions");

            OracleTransaction transaction;
            using (OracleConnection connection = GetConnection())
            {
                connection.Open();
                transaction = connection.BeginTransaction();

                try
                {
                    int permissionSetID;
                    if (!editPermissionsData.ModifyOriginalPermissions)
                    {
                        permissionSetID = ExecuteFunctionWithReturnID(
                            connection,
                            API_SYNONYM + ".ClonePermissionSetToPage",
                            new[] {
                                new QueryParameter("i_PageID", pageID),
                                new QueryParameter("i_PermissionSetID", editPermissionsData.PermissionSet.ID)
                            });
                    }
                    else
                    {
                        permissionSetID = editPermissionsData.PermissionSet.ID;
                        ExecuteProcedure(
                            connection,
                            API_SYNONYM + ".RemovePermissions",
                            new[] {
                                new QueryParameter("i_PermissionSetID", permissionSetID)
                            });
                    }

                    foreach (var rolePrivilege in editPermissionsData.PermissionSet.RolePrivileges)
                    {
                        ExecuteProcedure(
                            connection,
                            API_SYNONYM + ".AddPermissions",
                            new[] {
                                new QueryParameter("i_PermissionSetID", permissionSetID),
                                new QueryParameter("i_RoleName", rolePrivilege.RoleName),
                                new QueryParameter("i_ViewPage", rolePrivilege.Privileges.ViewPage ? "Y" : "N"),
                                new QueryParameter("i_EditPage", rolePrivilege.Privileges.EditPage ? "Y" : "N"),
                                new QueryParameter("i_CreatePage", rolePrivilege.Privileges.CreatePage ? "Y" : "N"),
                                new QueryParameter("i_CreateFolder", rolePrivilege.Privileges.CreateFolder ? "Y" : "N"),
                                new QueryParameter("i_EditPermissions", rolePrivilege.Privileges.EditPermissions ? "Y" : "N"),
                                new QueryParameter("i_ManageRevisions", rolePrivilege.Privileges.ManageRevisions ? "Y" : "N"),
                                new QueryParameter("i_ManagePublishing", rolePrivilege.Privileges.ManagePublishing ? "Y" : "N")
                            });
                    }

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "SavePagePermissions");
        }

        public void RemovePageSpecificPermissions(int pageID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "RemovePageSpecificPermissions");

            OracleTransaction transaction;
            using (OracleConnection connection = GetConnection())
            {
                connection.Open();
                transaction = connection.BeginTransaction();

                try
                {
                    ExecuteProcedure(
                        connection,
                        API_SYNONYM + ".RemovePageSpecificPermissions",
                        new[] {
                            new QueryParameter("i_PageID", pageID)
                        });

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "RemovePageSpecificPermissions");
        }

        public PageContent GetPageContent(int pageID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetPageContent");

            PageContent content = GetPageContentInternal(pageID, WikiController.HEAD_REVISION);

            if (content == null)
            {
                throw new PageNotFoundException(pageID);
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetPageContent");
            return content;
        }

        public PageContent GetPageContentForPending(int pageID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetPageContent");

            PageContent content = GetPageContentInternal(pageID, WikiController.PENDING_REVISION);

            if (content == null)
            {
                throw new PageNotFoundException(pageID);
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetPageContent");
            return content;
        }

        public PageContent GetPageContentForRevision(int pageID, int revisionNumber)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetPageContentForRevision");

            PageContent content = GetPageContentInternal(pageID, revisionNumber);

            if (content == null)
            {
                throw new PageNotFoundException(pageID, revisionNumber);
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetPageContentForRevision");
            return content;
        }

        public IEnumerable<AvailablePageLink> GetAvailablePageLinks(string folder, int pageID, bool removeSuppliedPage)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetAvailablePageLinks");

            List<AvailablePageLink> links = new List<AvailablePageLink>();

            using (OracleConnection connection = GetConnection())
            {
                connection.Open();

                OracleDataReader reader = GetCursorFunctionReader(connection,
                    QUERY_SYNONYM + ".GetAvailablePageLinks",
                    new[] {
                        new QueryParameter("i_Folder", folder),
                        new QueryParameter("i_PageID", pageID)
                    });

                while (reader.Read())
                {
                    int queriedPageID = reader["pageID"].As<int>();
                    if (!removeSuppliedPage || queriedPageID != pageID)
                    {
                        links.Add(new AvailablePageLink(
                            queriedPageID,
                            reader["pageTitle"].As<string>()));
                    }
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetAvailablePageLinks");
            return links;
        }

        public void UpdatePage(int pageID, string author, PageContent content, bool publish)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "UpdatePage");

            OracleTransaction transaction;
            using (OracleConnection connection = GetConnection())
            {
                connection.Open();
                transaction = connection.BeginTransaction();

                try
                {
                    int newRevisionNumber = ExecuteFunctionWithReturnID(
                        connection,
                        API_SYNONYM + ".UpdatePage",
                        new[] {
                            new QueryParameter("i_PageID", pageID),
                            new QueryParameter("i_Author", author),
                            new QueryParameter("i_PageTitle", content.Title),
                            new QueryParameter("i_PageContent", content.Text),
                            new QueryParameter("i_Publish", publish ? "Y" : "N")
                        });

                    foreach (var pageLink in content.Links)
                    {
                        ExecuteProcedure(
                            connection,
                            API_SYNONYM + ".AddPageLink",
                            new[] {
                                new QueryParameter("i_PageID", pageID),
                                new QueryParameter("i_RevisionNumber", newRevisionNumber),
                                new QueryParameter("i_PageLinkKey", pageLink.PageLinkKey),
                                new QueryParameter("i_DisplayText", pageLink.DisplayText),
                                new QueryParameter("i_LinkPageID", pageLink.LinkPageID),
                                new QueryParameter("i_Target", pageLink.Target)
                            });
                    }

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "UpdatePage");
        }

        public IEnumerable<PageInformation> GetPageRevisionList(int pageID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetPageRevisionList");

            List<PageInformation> revisions = new List<PageInformation>();

            using (OracleConnection connection = GetConnection())
            {
                connection.Open();
                OracleDataReader reader = GetCursorFunctionReader(connection,
                    QUERY_SYNONYM + ".GetPageRevisionList",
                    new[] {
                        new QueryParameter("i_PageID", pageID)
                    });

                while (reader.Read())
                {
                    revisions.Add(new PageInformation(
                        pageID,
                        reader["pageName"].As<string>(),
                        reader["pageTitle"].As<string>(),
                        reader["revisionNumber"].As<int>(),
                        reader["revisionDate"].As<DateTime>(),
                        reader["revisionAuthor"].As<string>(),
                        true,
                        reader["revisionStatus"].As<string>()));
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetPageRevisionList");
            return revisions;
        }

        public RetentionSettings GetRetentionSettings(int pageID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetRetentionSettings");

            RetentionSettings retentionSettings;

            using (OracleConnection connection = GetConnection())
            {
                connection.Open();
                OracleDataReader reader = GetCursorFunctionReader(connection,
                    QUERY_SYNONYM + ".GetRetentionSettings",
                    new[] {
                        new QueryParameter("i_PageID", pageID)
                    });

                if (reader.Read())
                {
                    retentionSettings = new RetentionSettings(
                        pageID,
                        (RetentionUnit)Enum.Parse(typeof(RetentionUnit), reader["retentionUnit"].As<string>("None")),
                        reader["retentionMeasure"].As<int>(0));
                }
                else
                {
                    throw new PageNotFoundException(pageID);
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetRetentionSettings");
            return retentionSettings;
        }

        public void SaveRetentionSettings(RetentionSettings retentionSettings)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "SaveRetentionSettings");

            OracleTransaction transaction;
            using (OracleConnection connection = GetConnection())
            {
                connection.Open();
                transaction = connection.BeginTransaction();

                try
                {
                    ExecuteProcedure(
                        connection,
                        API_SYNONYM + ".SaveRetentionSettings",
                        new[] {
                            new QueryParameter("i_PageID", retentionSettings.PageID),
                            new QueryParameter("i_RetentionUnit", retentionSettings.Unit.ToString()),
                            new QueryParameter("i_RetentionMeasure", retentionSettings.Measure)
                        });

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "SaveRetentionSettings");
        }

        public PublicationSettings GetPublicationSettings(int pageID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetPublicationSettings");

            PublicationSettings publicationSettings;

            using (OracleConnection connection = GetConnection())
            {
                connection.Open();
                OracleDataReader reader = GetCursorFunctionReader(connection,
                    QUERY_SYNONYM + ".GetPublicationSettings",
                    new[] {
                        new QueryParameter("i_PageID", pageID)
                    });

                if (reader.Read())
                {
                    publicationSettings = new PublicationSettings(
                        reader["publishable"].Equals("Y"));
                }
                else
                {
                    throw new PageNotFoundException(pageID);
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetPublicationSettings");
            return publicationSettings;
        }

        public void SavePublicationSettings(int pageID, PublicationSettings publicationSettings)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "SavePublicationSettings");

            OracleTransaction transaction;
            using (OracleConnection connection = GetConnection())
            {
                connection.Open();
                transaction = connection.BeginTransaction();

                try
                {
                    ExecuteProcedure(
                        connection,
                        API_SYNONYM + ".SavePublicationSettings",
                        new[] {
                            new QueryParameter("i_PageID", pageID),
                            new QueryParameter("i_Publishable", publicationSettings.Publishable ? "Y" : "N")
                        });

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "SavePublicationSettings");
        }

        public void PublishPage(int pageID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "PublishPage");

            OracleTransaction transaction;
            using (OracleConnection connection = GetConnection())
            {
                connection.Open();
                transaction = connection.BeginTransaction();

                try
                {
                    ExecuteProcedure(
                        connection,
                        API_SYNONYM + ".PublishPage",
                        new[] {
                            new QueryParameter("i_PageID", pageID)
                        });

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "PublishPage");
        }

        public void DeletePendingRevision(int pageID)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "DeletePendingRevision");

            OracleTransaction transaction;
            using (OracleConnection connection = GetConnection())
            {
                connection.Open();
                transaction = connection.BeginTransaction();

                try
                {
                    ExecuteProcedure(
                        connection,
                        API_SYNONYM + ".DeletePendingRevision",
                        new[] {
                            new QueryParameter("i_PageID", pageID)
                        });

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "DeletePendingRevision");
        }

        #endregion

        #region Private Methods

        public PageContent GetPageContentInternal(int pageID, int revisionNumber)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetPageContentInternal");

            PageContent content = null;
            string pageContentFunctionName = QUERY_SYNONYM + ".GetPageContent";
            string pageLinksFunctionName = QUERY_SYNONYM + ".GetPageLinks";
            List<QueryParameter> pageContentParameters = new List<QueryParameter>(2);
            List<QueryParameter> pageLinksParameters = new List<QueryParameter>(2);

            QueryParameter pageIDParameter = new QueryParameter("i_PageID", pageID);
            pageContentParameters.Add(pageIDParameter);
            pageLinksParameters.Add(pageIDParameter);

            // Modify the queries and parameters for specific revision retrieval
            if (revisionNumber == WikiController.PENDING_REVISION)
            {
                pageContentFunctionName += "ForPending";
                pageLinksFunctionName += "ForPending";
            }
            else if (revisionNumber != WikiController.HEAD_REVISION)
            {
                pageContentFunctionName += "ForRevision";
                pageLinksFunctionName += "ForRevision";

                QueryParameter revisionNumberParameter = new QueryParameter("i_RevisionNumber", revisionNumber);
                pageContentParameters.Add(revisionNumberParameter);
                pageLinksParameters.Add(revisionNumberParameter);
            }

            using (OracleConnection connection = GetConnection())
            {
                connection.Open();

                OracleDataReader reader = GetCursorFunctionReader(connection,
                    pageContentFunctionName,
                    pageContentParameters);

                if (reader.HasRows)
                {
                    reader.Read();

                    string title = reader["pageTitle"].As<string>();
                    string text = reader["pageContent"].As<string>();

                    reader = GetCursorFunctionReader(connection,
                        pageLinksFunctionName,
                        pageLinksParameters);

                    List<InternalPageLink> links = new List<InternalPageLink>();
                    while (reader.Read())
                    {
                        links.Add(new InternalPageLink(
                            reader["linkPageID"].As<int>(),
                            reader["pageLinkKey"].As<int>(),
                            reader["linkUrl"].As<string>(),
                            reader["displayText"].As<string>(),
                            reader["target"].As<string>(string.Empty)));
                    }

                    content = new PageContent(title, text, links);
                }
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetPageContentInternal");
            return content;
        }

        private PermissionSet GetPagePermissionsInternal(OracleConnection connection, int pageID, out bool pageSpecificPermissions)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetPagePermissionsInternal");

            OracleDataReader reader = GetCursorFunctionReader(connection,
                QUERY_SYNONYM + ".GetPagePermissions",
                new[] {
                        new QueryParameter("i_PageID", pageID)
                    });

            PermissionSet permissionSet;
            if (reader.HasRows)
            {
                reader.Read();
                permissionSet = new PermissionSet(reader["permissionSetID"].As<int>());
                pageSpecificPermissions = reader["pageSpecific"].Equals("Y");
                do
                {
                    RolePrivileges privileges = permissionSet.AddRole(reader["roleName"].As<string>());
                    LoadRolePrivileges(reader, privileges);
                } while (reader.Read());
            }
            else
            {
                throw new InvalidDataStateException("GetPagePermissions");
            }

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetPagePermissionsInternal");
            return permissionSet;
        }

        #endregion

        #region Private Infrastructure Methods

        private OracleConnection GetConnection()
        {
            return new OracleConnection(connectionString);
        }

        private OracleDataReader GetCursorFunctionReader(OracleConnection connection, string query, IEnumerable<QueryParameter> parameters)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "GetCursorFunctionReader");

            OracleCommand command = connection.CreateCommand();
            command.BindByName = true;
            command.CommandText = query;
            TraceManager.WriteFormat(TraceComponent.DataProvider, TraceLevel.Info, "CommandText: {0}", command.CommandText);
            command.CommandType = CommandType.StoredProcedure;
            foreach (var parameter in parameters)
            {
                command.Parameters.Add(parameter.Name, parameter.Value);
                TraceManager.ParameterValue(TraceComponent.DataProvider, parameter.Name, parameter.Value);
            }
            command.Parameters.Add("returnCursor", OracleDbType.RefCursor, ParameterDirection.ReturnValue);

            TraceManager.EndMethod(TraceComponent.DataProvider, "GetCursorFunctionReader");
            return command.ExecuteReader();
        }

        private int ExecuteFunctionWithReturnID(OracleConnection connection, string function, IEnumerable<QueryParameter> parameters)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "ExecuteFunctionWithReturnID");

            OracleCommand command = connection.CreateCommand();
            command.BindByName = true;
            command.CommandText = function;
            TraceManager.WriteFormat(TraceComponent.DataProvider, TraceLevel.Info, "CommandText: {0}", command.CommandText);
            command.CommandType = CommandType.StoredProcedure;
            foreach (var parameter in parameters)
            {
                command.Parameters.Add(parameter.Name, parameter.Value);
                TraceManager.ParameterValue(TraceComponent.DataProvider, parameter.Name, parameter.Value);
            }
            OracleParameter returnValue = new OracleParameter("returnValue", OracleDbType.Int32, ParameterDirection.ReturnValue);
            command.Parameters.Add(returnValue);
            command.ExecuteNonQuery();

            int returnID = returnValue.Value.As<int>();
            TraceManager.VariableValue(TraceComponent.DataProvider, "returnID", returnID);

            TraceManager.EndMethod(TraceComponent.DataProvider, "ExecuteFunctionWithReturnID");
            return returnID;
        }

        private void ExecuteProcedure(OracleConnection connection, string procedure, IEnumerable<QueryParameter> parameters)
        {
            TraceManager.BeginMethod(TraceComponent.DataProvider, "ExecuteProcedure");

            OracleCommand command = connection.CreateCommand();
            command.BindByName = true;
            command.CommandText = procedure;
            TraceManager.WriteFormat(TraceComponent.DataProvider, TraceLevel.Info, "CommandText: {0}", command.CommandText);
            command.CommandType = CommandType.StoredProcedure;
            foreach (var parameter in parameters)
            {
                command.Parameters.Add(parameter.Name, parameter.Value);
                TraceManager.ParameterValue(TraceComponent.DataProvider, parameter.Name, parameter.Value);
            }
            command.ExecuteNonQuery();

            TraceManager.EndMethod(TraceComponent.DataProvider, "ExecuteProcedure");
        }

        internal static object ConvertToType(object value, Type newType, object nullReplacement = null)
        {
            if (nullReplacement != null &&
                (
                    value == null ||
                    value is DBNull ||
                    (
                        value is OracleDecimal && ((OracleDecimal)value).IsNull
                    ) ||
                    (
                        value is OracleString && ((OracleString)value).IsNull
                    )
                )
               )
            {
                return nullReplacement;
            }
            else
            {
                object result = value;

                if (result is OracleDecimal)
                {
                    result = ((OracleDecimal)result).Value;
                }
                else if (result is OracleString)
                {
                    OracleString oString = (OracleString)result;
                    result = oString.IsNull ? string.Empty : oString.Value;
                }

                return Convert.ChangeType(result, newType);
            }
        }

        #endregion

        #region Object Loaders

        private static void LoadRolePrivileges(OracleDataReader reader, RolePrivileges privileges)
        {
            privileges.Privileges.ViewPage = reader["viewPage"].Equals("Y");
            privileges.Privileges.EditPage = reader["editPage"].Equals("Y");
            privileges.Privileges.CreatePage = reader["createPage"].Equals("Y");
            privileges.Privileges.CreateFolder = reader["createFolder"].Equals("Y");
            privileges.Privileges.EditPermissions = reader["editPermissions"].Equals("Y");
            privileges.Privileges.ManageRevisions = reader["manageRevisions"].Equals("Y");
            privileges.Privileges.ManagePublishing = reader["managePublishing"].Equals("Y");
        }

        private static PageInformation LoadPageInformation(OracleDataReader reader)
        {
            return new PageInformation(
                reader["pageID"].As<int>(),
                reader["pageName"].As<string>(),
                reader["pageTitle"].As<string>(),
                reader["revisionNumber"].As<int>(),
                reader["revisionDate"].As<DateTime>(),
                reader["revisionAuthor"].As<string>(),
                reader["versioned"].Equals("Y"),
                reader["revisionStatus"].As<string>());
        }

        private static PageDetails LoadPageDetails(OracleDataReader reader)
        {
            return new PageDetails(
                reader["secured"].Equals("Y"),
                reader["versioned"].Equals("Y"),
                reader["publishable"].Equals("Y"),
                reader["pendingRevision"].Equals("Y"),
                reader["pendingRevisionAuthor"].As<string>(string.Empty));
        }

        #endregion
    }

    static class ExtensionMethods
    {
        public static T As<T>(this object item)
        {
            return (T)OracleWikiDataProvider.ConvertToType(item, typeof(T));
        }

        public static T As<T>(this object item, object nullReplacement)
        {
            return (T)OracleWikiDataProvider.ConvertToType(item, typeof(T), nullReplacement);
        }

    }
}
